package com.cupk.repository;

import com.cupk.entity.Movie;
import com.cupk.util.JDBCTools;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author LY
 * @version 1.0
 */
public class MovieRepository {
    //连接池
    private static ComboPooledDataSource dataSource;

    static {
        dataSource = new ComboPooledDataSource("testc3p0");
    }


    //找到所有电影
    public List<Movie> findAll() {
        Connection connection = null;
        List<Movie> list = new ArrayList<>();
        try {
            connection = dataSource.getConnection();
            String sql = "select * from movieList ";
            QueryRunner queryRunner = new QueryRunner();
            list = queryRunner.query(connection, sql, new BeanListHandler<>(Movie.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }


    //找到所有含关键字的电影,并返回结果列表
    public List<Movie> findMovies(String movieName) {
        Connection connection = null;
        List<Movie> list = new ArrayList<>();
        try {
            connection = dataSource.getConnection();
            String sql = "select * from movieList where name LIKE " + "\"%" + movieName + "%\"";
            QueryRunner queryRunner = new QueryRunner();
            list = queryRunner.query(connection, sql, new BeanListHandler<>(Movie.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }

    //找到同类型所有电影，并返回列表
    public List<Movie> findByType(String type,boolean limit) {
        Connection connection = null;
        List<Movie> list = new ArrayList<>();
        try {
            connection = dataSource.getConnection();
            String sql = null;
            if(limit){  //对搜索结果有限制
                sql = "select * from movieList where type LIKE " + "\"%" + type + "%\"" + "LIMIT 6;";
            }else{  //对搜索结果无限制
                sql = "select * from movieList where type LIKE " + "\"%" + type + "%\";";
            }

            QueryRunner queryRunner = new QueryRunner();
            list = queryRunner.query(connection, sql, new BeanListHandler<>(Movie.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }

    //按地区查找电影，并返回列表
    public List<Movie> findByArea(String area) {
        Connection connection = null;
        List<Movie> list = new ArrayList<>();
        try {
            connection = dataSource.getConnection();
            String sql = null;
            if(area.equals("欧美")){  //搜索结果范围大
                sql = "select * from movieList where area = " + "\"" + "美国" + "\"" + " OR " +  " area = " + "\"" + "法国" + "\"" + " OR " + "area = " + "\"" + "英国" + "\"" + " OR " + "area = " + "\"" + "意大利" + "\"" + " OR " + "area = " + "\"" + "德国" + "\""+ ";";
            }else{  //搜索范围小
                sql = "select * from movieList where area LIKE " + "\"%" + area + "%\";";
            }

            QueryRunner queryRunner = new QueryRunner();
            list = queryRunner.query(connection, sql, new BeanListHandler<>(Movie.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }

    //根据电影的id找到详情页
    public Movie findById(int id) {
        Connection connection = null;
        Movie movie = null;
        try {
            connection = dataSource.getConnection();
            String sql = "select * from movieList where id = " +"\""+id+"\"";
            QueryRunner queryRunner = new QueryRunner();
            movie = queryRunner.query(connection,sql,new BeanHandler<>(Movie.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return movie;
    }

    //更新电影评分
    public void updateGrade(Integer id,Double grade,Integer number)
    {
        Connection connection = JDBCTools.getConnection();
        String sql = "UPDATE movieList SET grade = ?,number = ? WHERE id = ?;";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setDouble(1,grade);
            preparedStatement.setInt(2,number);
            preparedStatement.setInt(3,id);
            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCTools.release(connection, preparedStatement, null);
        }
    }

    public List<Movie> findByIdGroup(Integer[] collectible){
        Connection connection = null;
        List<Movie> list = new ArrayList<>();
        try {
            connection = dataSource.getConnection();
            String sql = "SELECT * FROM movieList WHERE ";
            for (int i = 0; i < collectible.length; i++) {
                sql += "id = " + collectible[i] + " OR ";
            }
            sql = sql.substring(0,sql.length()-4);
            sql+=";";
            QueryRunner queryRunner = new QueryRunner();
            list = queryRunner.query(connection, sql, new BeanListHandler<>(Movie.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }

    //添加评论
    public void addComments(String comment,Integer id){
        Connection connection = JDBCTools.getConnection();
        String sql = "UPDATE movieList SET comment = ? WHERE id = ?;";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,comment);
            preparedStatement.setInt(2,id);
            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCTools.release(connection, preparedStatement, null);
        }
    }

    //添加新电影
    public void addMovie(String name,String year,String direct,String actor,String area,String type,String content,Integer length,String language ){
        Connection connection = JDBCTools.getConnection();
        String sql = "INSERT INTO `movieList` (`name`, `year`, `direct`, `actor`, `area`, `type`, `content`, `length`, `language`) VALUES (?,?,?,?,?,?,?,?,?);";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            preparedStatement.setString(2,year);
            preparedStatement.setString(3,direct);
            preparedStatement.setString(4,actor);
            preparedStatement.setString(5,area);
            preparedStatement.setString(6,type);
            preparedStatement.setString(7,content);
            preparedStatement.setInt(8,length);
            preparedStatement.setString(9,language);
            int result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection, preparedStatement, null);
        }
    }

}
